Networth Corp : Data Science Assignment

Contact Information

• Name - Owais Ahmad
• Phone +919515884381
• Email Owaiskhan9654@gmail.com

Objective: To increase conversions for customers.

Question: What would you do keeping in mind that the customer uses this search product? How can you use this data to bring out insights to improve the search product?

Definitions of some terms:

• Hits - Number of Search Queries

• Click-throughs - Number of times visitors clicked on any one of the results in the search listing page

• Clicks - Cumulative Number of times visitors clicked on any result in the search listing page

• Clicks would always be greater or equal to than clickthroughs

• CTR = Clickthrough rate = Click Throughs/Hits

• Orders - Orders placed against the search query (Query report) or for the particular product)

• You can ignore the initial price and final price from your analysis

import pandas as pd
import plotly.express as px
from pandas_profiling import ProfileReport
import numpy as np
import plotly.graph_objects as go

df = pd.read_excel('Customer Raw data.xlsx', sheet_name=2)
df

c:\users\admin\appdata\local\programs\python\python38\lib\site-packages\openpyxl\worksheet\_reader.py:312: UserWarning: Unknown extension is not supported and will be removed
  warn(msg)

	query	hits	clicks	clickThroughs	ctr	carts	orders
0	pink	3971	330	93	2.34	18	0
1	Chambray	612	1360	447	73.04	145	8
2	gingham in green and white	601	0	0	0.00	0	0
3	white dresses	486	800	310	63.79	73	1
4	grey	480	89	28	5.83	10	0
...	...	...	...	...	...	...	...
495	Pleated knit	14	12	6	42.86	0	0
496	Contrast	14	27	7	50.00	4	0
497	1940s	14	8	4	28.57	4	0
498	crimson	14	19	5	35.71	8	0
499	watercolor	14	27	9	64.29	0	0

500 rows × 7 columns

profile = ProfileReport(df, title="Customer Unbxd Query performance Report")

profile.to_notebook_iframe()

len(df.groupby(['query']))

500

Top 50 Queries hits v/s Orders placed

fig = px.bar(df.sort_values(
    ['hits'],
    ascending=False,
)[0:50],
             x='query',
             color='orders',
             y='orders',
             color_continuous_scale='Reds')

fig.update_layout(
    xaxis_tickangle=90,
    title_text=
    'Top 50 Queries hits v/s Orders placed (Please Zoom to Have Detailed Analysis)'
)

fig.show()

Scatter Plot Between Features Query, Hits,Clicks, Click Throughs, Carts and Orders (Please ZoomScatter Plot or map Area)

features = ["hits", "clicks", "clickThroughs", "carts"]

fig = px.scatter_matrix(df.sort_values(
    ['hits'],
    ascending=False,
),
                        dimensions=features,
                        color="orders",
                        hover_data=['query'])
fig.update_traces(diagonal_visible=False, )
fig.update_layout(
    title_text=
    'Scatter Plot Between Features Query, Hits,Clicks, Click Throughs, Carts and Orders (Please Zoom)'
)
fig.show()

Parallel Coordinates Plot Between Features Query, Hits,Clicks, Click Throughs, Carts and Orders (Please Zoom or select Region in specific line)

fig = px.parallel_coordinates(
    df,
    color="orders",
    color_continuous_scale=px.colors.diverging.Tealrose,
    color_continuous_midpoint=4)

fig.update_layout(title_text='Features Relation Mapping Among ')
fig.show()

Query Wise Funnel Cycles for Top 20 CTR Percentage

fig = px.funnel(df.sort_values(
    ['ctr'],
    ascending=False,
)[0:20],
                x='ctr',
                y='query',
                color='query')

fig.update_layout(
    title_text='Query Wise Funnel Cycles for Top 20 CTR Percentage')
fig.show()

Top_ctr1 = df.sort_values(
    ['ctr'],
    ascending=False,
)[0:1]
Top_ctr2 = df.sort_values(
    ['ctr'],
    ascending=False,
)[1:2]
Top_ctr3 = df.sort_values(
    ['ctr'],
    ascending=False,
)[2:3]
Top_ctr4 = df.sort_values(
    ['ctr'],
    ascending=False,
)[3:4]
Top_ctr5 = df.sort_values(
    ['ctr'],
    ascending=False,
)[4:5]

Top_ctr1 = Top_ctr1[[
    'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr2 = Top_ctr2[[
    'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr3 = Top_ctr3[[
    'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr4 = Top_ctr4[[
    'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]
Top_ctr5 = Top_ctr5[[
    'clicks', 'hits', 'clickThroughs', 'carts', 'orders', 'ctr', 'query'
]]

Complete Funnel Cycles for Top 5 CTR values for Queries

y = ['clicks', 'hits', 'clickThroughs', 'carts', 'orders']
fig = go.Figure()

fig.add_trace(
    go.Funnel(name=Top_ctr1['query'].values[0],
              y=y,
              x=Top_ctr1.drop(columns=['query', 'ctr']).values[0],
              textinfo="value+percent initial"))

fig.add_trace(
    go.Funnel(name=Top_ctr2['query'].values[0],
              y=y,
              x=Top_ctr2.drop(columns=['query', 'ctr']).values[0],
              textinfo="value+percent initial"))

fig.add_trace(
    go.Funnel(name=Top_ctr3['query'].values[0],
              y=y,
              x=Top_ctr3.drop(columns=['query', 'ctr']).values[0],
              textinfo="value+percent initial"))

fig.add_trace(
    go.Funnel(name=Top_ctr4['query'].values[0],
              y=y,
              x=Top_ctr4.drop(columns=['query', 'ctr']).values[0],
              textinfo="value+percent initial"))

fig.add_trace(
    go.Funnel(name=Top_ctr5['query'].values[0],
              y=y,
              x=Top_ctr5.drop(columns=['query', 'ctr']).values[0],
              textinfo="value+percent initial"))

fig.update_layout(title_text='Complete Funnel Cycles for Top 5 CTR values for Queries')
fig.show()

df_0 = pd.read_excel('Customer Raw data.xlsx', sheet_name=0)
df_0

c:\users\admin\appdata\local\programs\python\python38\lib\site-packages\openpyxl\worksheet\_reader.py:312: UserWarning:

Unknown extension is not supported and will be removed

	PID	Product Name	Category	Final Price	Initial Price	Color	Primary Color	Fabric
0	CL0036386	Ikat stripe print cotton knit dress	Dress	84.95	33.95	Blue/navy/white	Blue	Cotton Spandex
1	CL0030567	Feminine pleated knit dress	Dress	59.95	41.95	Navy blue	Navy blue	Cotton Spandex
2	CL0032320	Floral embellished chambray denim maxi dress	Dress	96.95	67.95	Deep indigo	Deep indigo	Cotton
3	CL0036700	Dot print cap sleeve shirt	Top	49.95	19.95	White/navy	White	Cotton
4	CL0036365	Layered asymmetric hem hacci knit top	Top	69.95	27.95	Blue	Blue	Polyester
...	...	...	...	...	...	...	...	...
711	CL0031288	Ruffle front poplin dress	Dress	69.95	52.95	Spring green	Spring green	Cotton
712	CL0032199	Beaded cotton poplin sheath dress	Dress	86.95	65.95	Spring green	Spring green	Cotton
713	CL0031267	Embellished stripe waist poplin dress	Dress	76.95	57.95	Spring green multi	Spring green multi	Cotton
714	CL0031287	Sash waist poplin skirt	Skirt	59.95	44.95	Spring green	Spring green	Cotton
715	CL0031947	Colorblock retro poplin frock	Dress	59.95	44.95	Black/spring green	Black	Cotton

716 rows × 8 columns

df_1 = pd.read_excel('Customer Raw data.xlsx', sheet_name=1)
df_1

c:\users\admin\appdata\local\programs\python\python38\lib\site-packages\openpyxl\worksheet\_reader.py:312: UserWarning:

Unknown extension is not supported and will be removed

	pId	pName	clicks	carts	orders
0	CL0034516	Chevron stripe colorblock maxi dress	29144	2181	204
1	CL0037085	Chevron stripe colorblock maxi dress	25892	237	70
2	CL0036758	Bird print pleated cotton knit dress	16343	340	97
3	CL0032310	Cotton knit fit and flare dress	16234	350	99
4	CL0037110	Pleat front chambray denim maxi dress	13317	229	56
...	...	...	...	...	...
2496	CL0036376	Name N/A	0	0	1
2497	CL0035627	Heart print cotton A-line dress	0	0	1
2498	GC0000202	Name N/A	0	0	5
2499	GC0000204	Name N/A	0	0	7
2500	GC0000205	Name N/A	0	0	11

2501 rows × 5 columns

df_1.rename({'pId': 'PID'}, axis='columns', inplace=True)

df_1 = pd.merge(df_0, df_1, on="PID").drop(columns='pName')
df_1

	PID	Product Name	Category	Final Price	Initial Price	Color	Primary Color	Fabric	clicks	carts	orders
0	CL0036386	Ikat stripe print cotton knit dress	Dress	84.95	33.95	Blue/navy/white	Blue	Cotton Spandex	1846	43	15
1	CL0030567	Feminine pleated knit dress	Dress	59.95	41.95	Navy blue	Navy blue	Cotton Spandex	6882	242	56
2	CL0032320	Floral embellished chambray denim maxi dress	Dress	96.95	67.95	Deep indigo	Deep indigo	Cotton	10680	72	27
3	CL0036700	Dot print cap sleeve shirt	Top	49.95	19.95	White/navy	White	Cotton	2051	59	24
4	CL0036365	Layered asymmetric hem hacci knit top	Top	69.95	27.95	Blue	Blue	Polyester	4630	123	50
...	...	...	...	...	...	...	...	...	...	...	...
711	CL0031288	Ruffle front poplin dress	Dress	69.95	52.95	Spring green	Spring green	Cotton	62	8	0
712	CL0032199	Beaded cotton poplin sheath dress	Dress	86.95	65.95	Spring green	Spring green	Cotton	380	4	0
713	CL0031267	Embellished stripe waist poplin dress	Dress	76.95	57.95	Spring green multi	Spring green multi	Cotton	175	16	2
714	CL0031287	Sash waist poplin skirt	Skirt	59.95	44.95	Spring green	Spring green	Cotton	108	15	0
715	CL0031947	Colorblock retro poplin frock	Dress	59.95	44.95	Black/spring green	Black	Cotton	2774	102	24

716 rows × 11 columns

df_1['Category'].value_counts()

 Dress       428
 Top         147
 Skirt        72
 Pant         49
 Jumpsuit     11
 Jacket        9
Name: Category, dtype: int64

df_1.sort_values(
    ['orders'],
    ascending=False,
)[0:50]

	PID	Product Name	Category	Final Price	Initial Price	Color	Primary Color	Fabric	clicks	carts	orders
669	CL0036758	Seagull print pleated cotton knit dress	Dress	89.95	67.95	Light blue/white/black	Light blue	Cotton Spandex	16343	340	97
11	CL0030370	Pleat waist poplin shirtdress	Dress	59.95	41.95	Deep navy	Deep navy	Cotton	9571	367	89
114	CL0032653	Cotton poplin A-line shirtdress	Dress	59.95	41.95	Black	Black	Cotton	8287	247	82
325	CL0036806	Retro chambray shirtdress	Dress	79.95	55.95	Indigo	Indigo	Cotton	8407	227	71
653	CL0037085	Chevron stripe colorblock maxi dress	Dress	99.95	79.95	Black/white/eggplant	Black	Cotton	25892	237	70
698	CL0037156	Chevron stripe colorblock maxi dress	Dress	99.95	79.95	Spring green/white/deep navy	Spring green	Cotton	12364	172	68
670	CL0037110	Pleat front chambray denim maxi dress	Dress	69.95	52.95	Indigo	Indigo	Cotton	13317	229	56
1	CL0030567	Feminine pleated knit dress	Dress	59.95	41.95	Navy blue	Navy blue	Cotton Spandex	6882	242	56
13	CL0033768	Tie neck poplin dress	Dress	59.95	44.95	Black	Black	Cotton	7175	230	51
418	CL0034517	Chevron stripe colorblock maxi dress	Dress	99.95	79.95	Indigo/deep navy/khaki	Indigo	Cotton	6229	228	50
4	CL0036365	Layered asymmetric hem hacci knit top	Top	69.95	27.95	Blue	Blue	Polyester	4630	123	50
686	CL0037096	Elastic waist gingham maxi dress	Dress	69.95	52.95	Black/white	Black	Cotton	6848	103	42
15	CL0036693	Graphic flying bird print cotton dress	Dress	89.95	35.95	Black/off-white	Black	Cotton	2932	72	40
7	CL0035516	Floral vine print cotton dress	Dress	69.95	48.95	Off-white/teal multi	Off-white	Cotton Spandex	3390	85	38
5	CL0035569	Sash waist chambray dress	Dress	69.95	48.95	Indigo	Indigo	Cotton	5338	128	37
503	CL0033318	Kayla dress	Dress	59.95	59.95	Deep navy	Deep navy	Cotton	5174	181	37
675	CL0037049	Pinstripe cotton knit dress	Dress	59.95	44.95	Black/gray/white	Black	Cotton Spandex	3030	62	36
191	CL0032666	Embellished floral yoke poplin dress	Dress	96.95	67.95	Black multi	Black multi	Cotton	2904	88	34
680	CL0037039	Floral stripe print belted dress	Dress	59.95	44.95	Red/cream	Red	Cotton	5015	134	33
433	CL0037051	Bow tie back woven ikat check dress	Dress	69.95	52.95	Red/navy/white	Red	Cotton	8373	67	33
430	CL0027313	Chateau cotton poplin dress	Dress	59.95	41.95	Poppy red	Poppy red	Cotton	3645	79	31
46	CL0036797	Vintage denim chambray dress	Dress	64.95	45.95	Indigo	Indigo	Cotton	4264	124	29
642	CL0036972	Floral dot print high-low hem dress	Dress	99.95	69.95	Deep navy/yellow/white	Deep navy	Cotton	6335	77	29
610	CL0037102	Chevron stripe colorblock maxi dress	Dress	99.95	79.95	Poppy red/white/deep navy	Poppy red	Cotton	857	46	29
645	CL0036902	Graphic floral print colorblock dress	Dress	89.95	62.95	Black/cream/navy multi	Black	Cotton Spandex	3999	58	28
182	CL0033124	Chambray denim A-line shirtdress	Dress	69.95	48.95	Deep indigo	Deep indigo	Cotton	2333	142	28
8	CL0036337	Circle print diamond knit dress	Dress	59.95	41.95	White/green/black	White	Cotton Spandex	2818	66	28
540	CL0032313	Cotton knit fit and flare dress	Dress	69.95	34.95	Deep navy	Deep navy	Cotton Spandex	1426	91	27
217	CL0036485	Brooklyn dress	Dress	59.95	29.95	Brown	Brown	Cotton	3204	171	27
2	CL0032320	Floral embellished chambray denim maxi dress	Dress	96.95	67.95	Deep indigo	Deep indigo	Cotton	10680	72	27
25	CL0029095	Maritime tee	Top	49.95	34.95	Bottle green	Bottle green	Cotton Spandex	958	19	26
655	CL0037082	Pleated surplice cotton knit wrap dress	Dress	79.95	59.95	Purple	Purple	Cotton Spandex	3879	82	26
190	CL0036796	Chevron stripe colorblock maxi dress	Dress	99.95	79.95	White/black	White	Cotton	8280	71	26
66	CL0032757	Dahlia crinoline	Skirt	79.95	55.95	Black	Black	Polyester	3806	138	26
538	CL0036617	Seamed fit-and flare sateen dress	Dress	79.95	31.95	Mayo teal	Mayo teal	Cotton Spandex	2169	72	26
44	CL0036798	Citron floral print dress	Dress	64.95	45.95	Citron multi	Citron multi	Cotton Spandex	2559	97	26
665	CL0026054	Shawl collar poplin shirtdress	Dress	59.95	41.95	Poppy red	Poppy red	Cotton	3487	111	26
120	CL0035883	Contrast trim poplin dress	Dress	64.95	45.95	Deep navy/white	Deep navy	Cotton	2977	110	25
48	CL0035581	Skater style cotton knit dress	Dress	64.95	45.95	Navy	Navy	Cotton Spandex	2720	44	25
69	CL0035605	Cowl neck birdsong dress	Dress	86.95	60.95	Navy blue	Navy blue	Cotton Spandex	2179	56	25
715	CL0031947	Colorblock retro poplin frock	Dress	59.95	44.95	Black/spring green	Black	Cotton	2774	102	24
91	CL0036809	Tie-neck cotton knit dress	Dress	64.95	48.95	Bottle green	Bottle green	Cotton Spandex	3693	82	24
3	CL0036700	Dot print cap sleeve shirt	Top	49.95	19.95	White/navy	White	Cotton	2051	59	24
23	CL0030235	Cotton knit curved waist dress	Dress	59.95	41.95	Navy blue	Navy blue	Cotton Spandex	1882	91	23
111	CL0036640	Polka dot voile print keyhole dress	Dress	89.95	44.95	Royal blue/off-white	Royal blue	Cotton	2323	83	23
20	CL0034047	Aurora dress	Dress	99.95	99.95	Black	Black	Cotton Nylon	2352	31	23
398	CL0037069	Bow tie cutout back cotton knit dress	Dress	74.95	56.95	Purple	Purple	Cotton Spandex	8231	51	23
105	CL0036393	Piped trim polka dot print dress	Dress	119.95	47.95	Off-white/navy	Off-white	Cotton	3096	64	22
410	CL0036929	Contrast collar woven check shirtdress	Dress	74.95	52.95	Beige/navy multi	Beige	Cotton	2199	44	21
127	CL0036747	Graphic mixed fruit print dress	Dress	109.95	43.95	Off-white/red/navy	Off-white	Cotton	2774	43	21

Detailed Compiled Report on both Product and Performance Report

profile = ProfileReport(
    df_1, title="Product Description and Performance Data Report")

profile.to_notebook_iframe()

df_1.groupby('Category').sum().drop(columns=['Final Price', 'Initial Price'])

	clicks	carts	orders
Category
Dress	718466	14922	3991
Jacket	5768	158	42
Jumpsuit	11006	148	31
Pant	23170	449	148
Skirt	55518	1590	396
Top	82008	2154	701

Clicks,Carts and Orders for Different Categories (Please Zoom to Have Detailed Analysis

fig = px.bar(df_1.groupby('Category').sum().drop(columns=['Final Price','Initial Price']).T,) \
             #x='orders',#df_1.groupby('Category').sum().drop(columns=['Final Price','Initial Price']).index,#color='orders',\

#y=['orders','clicks','carts'],color_continuous_scale='Reds', barmode="group",
#facet_row=df_1.groupby('Category').sum().drop(columns=['Final Price','Initial Price']).columns, )#facet_col="day",)

fig.update_layout(
    xaxis_tickangle=90,
    title_text='Clicks,Carts and Orders for Different \
Categories (Please Zoom to Have Detailed Analysis)',
    yaxis=dict(
        title='Total Count',
        titlefont_size=16,
        tickfont_size=14,
    ),
    xaxis=dict(
        title='Users Preference for Category in Clicks,Carts and Orders',
        titlefont_size=16,
        tickfont_size=14,
    ))

fig.show()

Clicks,Carts and Orders for Different Fabrics

fig = px.bar(
    df_1.groupby('Fabric').sum().drop(
        columns=['Final Price', 'Initial Price']).T, )

fig.update_layout(
    xaxis_tickangle=90,
    title_text=
    'Clicks,Carts and Orders for Different Fabrics (Please Zoom to Have Detailed Analysis)',
    yaxis=dict(
        title='Total Count',
        titlefont_size=16,
        tickfont_size=14,
    ),
    xaxis=dict(
        title='Users Preference for Fabric in Clicks,Carts and Orders',
        titlefont_size=16,
        tickfont_size=14,
    ))

fig.show()

Users Preferance of orders Based on Color of Dress

df_pie = df_1.copy()
df_pie.loc[df_pie['orders'] < 25, 'Primary Color'] = 'Other Colors'

fig = px.pie(df_pie, values='orders', names='Primary Color')
fig.update_layout(
    xaxis_tickangle=90,
    title_text='Users Preferance of orders Based on Color of orders',
)

fig.show()

conversions to the customers can be provided by having more Focus on Black Color and Indigo Color Dress as more users are Intrested in those Color.

Apart from this People Love more Cotton and Cotton spandix Fabric as maximum orders.